package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.ProductCategory;
import com.ysd.util.DBManager;


public class ProductCategoryDaoImpl {
	//查询所有分类的实现类
	public List<ProductCategory> getProductCategory() {
		List<ProductCategory> productList = new ArrayList<ProductCategory>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		


		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from productCategory");
			while(rs.next()) {
				int ProductCategoryId = rs.getInt("productCategoryId");
				String ProductCategoryName = rs.getString("productCategoryName");
				
				ProductCategory productCategory = new ProductCategory(ProductCategoryId, ProductCategoryName);

				productList.add(productCategory);
			}
			return productList;

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	public List<ProductCategory> getProductCategoryByPage(int page,int size) {
		List<ProductCategory> productList = new ArrayList<ProductCategory>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		


		try {
			st = conn.createStatement();
			String sql= "select *from productcategory limit "+(page-1)*size+","+size;
			System.out.println(sql);
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int ProductCategoryId = rs.getInt("productCategoryId");
				String ProductCategoryName = rs.getString("productCategoryName");
				
				ProductCategory productCategory = new ProductCategory(ProductCategoryId, ProductCategoryName);

				productList.add(productCategory);
			}
			return productList;

		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/*
	根据根据分类id删除产品分类
	did:要删除产品分类的ID
	return:返回受影响的行数
	*/
	public int deleteProductCateById(int dId) {
		return DBManager.executeUpdate("delete from  productcategory where productcategoryId = "+dId);
	}
	/**
	 * 添加产品分类，返回受影响行数
	 * @param pc
	 * @return
	 */
	public int addProductCategory(ProductCategory pc) {
		return DBManager.executeUpdate("INSERT into productcategory VALUES(null,'"+pc.getProductCategoryName()+"')");
	}
	/**
	 * 通过分类名称查询产品分类
	 * @param name
	 * @return
	 */
	public List<ProductCategory> addProductCate(String name){
		
			List<ProductCategory> productList = new ArrayList<ProductCategory>();
			
			Connection conn = DBManager.getConnection();
			Statement st = null;
			ResultSet rs = null;

			try {
				st = conn.createStatement();
				rs = st.executeQuery("select * from productcategory where productCategoryName='"+name+"'");
				while(rs.next()) {
					int ProductCategoryId = rs.getInt("productCategoryId");
					String ProductCategoryName = rs.getString("productCategoryName");
					
					ProductCategory productCategory = new ProductCategory(ProductCategoryId, ProductCategoryName);

					productList.add(productCategory);
				}
				return productList;

			} catch (SQLException e) {
				
				e.printStackTrace();
				return null;
			}finally {
				DBManager.closeAll(conn, st, rs);
			}
		}
	//返回list是因为结果集可能返回多条数据
	//返回单个对象是因为查到的结果有可能是一条
	public ProductCategory getProductById(int cId) {
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		ProductCategory  pc = null; 
		try {
			st=conn.createStatement();
			rs = st.executeQuery("select * from productcategory where productCategoryId = "+cId);
			while(rs.next()) {
				int productCategoryId = rs.getInt(1);//getInt(1);结果集得第一列
				String productCategoryName = rs.getString(2);
			    pc = new ProductCategory(productCategoryId, productCategoryName);
				
			}
			return pc;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	public int updateProcateById(ProductCategory pc) {
		String sql="update productcategory set productCategoryName='"+pc.getProductCategoryName()+"' where productCategoryId="+pc.getProductCategoryId();

		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	
	public int deleteProductAll(String proId) {
		return DBManager.executeUpdate("DELETE from productcategory where productcategoryId in("+proId+")");
	}
}


